<?php 
/**
 * Database Storage plugin
 *
 * @version		$Id: Database.php 161 2009-07-04 06:28:34Z dr_drsh $
 * @package		Joomla
 * @subpackage	JForms
 * @copyright	Copyright (C) 2008 Mostafa Muhammad. All rights reserved.
 * @license		GNU/GPL
 */

// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();

jimport('joomla.filesystem.folder');
jimport('joomla.filesystem.file');

class JFormSPluginDatabase {

    function getFSPath( $form, $fieldName, $recordId, $create=true ) {

        $tableName    = $form->storagePluginParameters['Database']['tableName'];
        if( $create ) {
            JFormSPluginDatabase::_createFSRecord( $tableName, $fieldName, $recordId );
        }
        return
                array(
                'path' => FS_PATH.$tableName.DS.$fieldName.DS.$recordId,
                'url'  => FS_URL.$tableName.'/'.$fieldName.'/'.$recordId
        );
    }

    function getNextInsertID( $form ) {

        $db =& JFactory::getDBO();

        $tableName    = $form->storagePluginParameters['Database']['tableName'];

        $db->setQuery('SHOW CREATE TABLE `#__jforms_'.$tableName.'`');
        $result = $db->loadRow();

        $nextAutoIndex = 1;
        $matches = array();
        preg_match('/AUTO_INCREMENT=(\d+)/', $result[1], $matches );
        if( count( $matches ) ) {
            $nextAutoIndex = intval( $matches[1] );
        }
        return $nextAutoIndex;
    }
    function _createEmptyHtmlFile( $path ) {
        if( !JFile::exists( $path.DS.'index.html' ) ) {
            JFile::write( $path.DS.'index.html','<html><body bgcolor="#FFFFFF"></body></html>' );
        }
    }
    function _createFSTable( $tableName ) {
        if( !JFolder::exists( FS_PATH.$tableName ) ) {
            JFolder::create( FS_PATH.$tableName );
            JFormSPluginDatabase::_createEmptyHtmlFile( FS_PATH.$tableName );
        }
    }

    function _deleteFSTable( $tableName ) {
        if( JFolder::exists( FS_PATH.$tableName ) ) {
            JFolder::delete( FS_PATH.$tableName );
        }
    }

    function _createFSField( $tableName, $fieldName ) {
        JFormSPluginDatabase::_createFSTable( $tableName );
        if( !JFolder::exists( FS_PATH.$tableName.DS.$fieldName ) ) {
            JFolder::create( FS_PATH.$tableName.DS.$fieldName );
            JFormSPluginDatabase::_createEmptyHtmlFile( FS_PATH.$tableName.DS.$fieldName );
        }
    }

    function _deleteFSField( $tableName, $fieldName ) {
        if( JFolder::exists( FS_PATH.$tableName.DS.$fieldName ) ) {
            JFolder::delete( FS_PATH.$tableName.DS.$fieldName );
        }
    }

    function _createFSRecord( $tableName, $fieldName, $recordId ) {

        JFormSPluginDatabase::_createFSTable( $tableName );
        JFormSPluginDatabase::_createFSField( $tableName, $fieldName );
        if( !JFolder::exists( FS_PATH.$tableName.DS.$fieldName.DS.$recordId ) ) {
            JFolder::create( FS_PATH.$tableName.DS.$fieldName.DS.$recordId );
            JFormSPluginDatabase::_createEmptyHtmlFile( FS_PATH.$tableName.DS.$fieldName.DS.$recordId  );

        }
    }

    function _deleteFSRecords( $tableName, $ids ) {

        $isDeleteAll = !is_array( $ids );

        $fields = JFolder::folders( FS_PATH.$tableName.DS );

        if( !$fields ) {
            return;
        }

        foreach( $fields as $field ) {
            $records = JFolder::folders( FS_PATH.$tableName.DS.$field.DS );
            foreach( $records as $record ) {
                if( $isDeleteAll || in_array( $record, $ids ) ) {
                    if( JFolder::exists( FS_PATH.$tableName.DS.$field.DS.$record ) ) {
                        JFolder::delete( FS_PATH.$tableName.DS.$field.DS.$record );
                    }
                }
            }
        }
    }

    function _getSQLField( $s, $n ) {

        switch($s->type) {

            case 'textfield':
                if( intval( $s->size ) > 255 ) {
                    $sql = ' `'.$n.'` TEXT NOT NULL';
                } else {
                    $sql = ' `'.$n.'` VARCHAR( '.$s->size.' ) NOT NULL';
                }
                break;

            case 'datetime':
                $sql = ' `'.$n.'` DATETIME NOT NULL DEFAULT "0000-00-00 00:00:00"';
                break;

            case 'date':
                $sql = ' `'.$n.'` DATE NOT NULL DEFAULT "0000-00-00"';
                break;

            case 'number':
                $sql = ' `'.$n.'` INT( '.$s->size.' ) NOT NULL';
                break;
        }
        return $sql;
    }

    function _clean( $array ) {

        $string = implode(',', $array);
        $string =  str_replace(array("\"","'","\\",";"),'', $string );
        $temp1 = explode( ',', $string );
        $temp2 = array();

        for($i=0;$i<count($temp1);$i++) {
            $temp1[$i] = trim($temp1[$i]);
            if( strlen( $temp1[$i] ) ) {
                $temp2[] = $temp1[$i];
            }
        }
        return implode( ',', $temp2 );

    }
    /**
     * Returns records from table based on $criteria
     *
     * @param $form   , Object : Form Object
     * @param $fields , Array  : Field names
     * @param $start  , int    : Starting record
     * @param $rpp    , int    : Records Per Page
     * @param $criteria , Object    : structured as
     *  $criteria->field_hash <- Object containing critieria specific to each field type
     * @access	public
     * @return	form object or null if not found/published
     */
    function searchRecords($form, $fields, $start, $rpp, $criteria ) {

        global $JFormGlobals;
        $db =& JFactory::getDBO();

        //Remove all unwanted characters, this array will only allow commas and alpha nuemrical values,
        //it is after all mySQL field names
        $requestedFields = JFormSPluginDatabase::_clean( $fields );
        $start = intval( $start );
        $rpp   = intval( $rpp );
        //Keyword cleanup is left to element plugins


        $pManager =& $JFormGlobals['JFormsPlugin'];
        $pManager->loadElementPlugins();

        $tableName = $form->storagePluginParameters['Database']['tableName'];

        $whereSQL = '';
        $whereFragments = array();

        //Contains fields that has been confirmed to be valid , i.e , existant in the current table
        $confirmedFieldsArray = explode( ',', JFORM_SYSTEM_FIELDS );

        /* This loop performs two task
		1) Check the validity of the incoming "$_GET['fields']"
		2) Collects the "Where" conditions from element plugins
        */
        $requestedFieldsArray = explode( ',', $requestedFields );

        //Sort form fields according to keywords order
        $arrangedFields = array();
        if( $criteria != null ) {
            $criteriaArray = JArrayHelper::fromObject( $criteria );
            foreach( $criteriaArray as $hash => $c ) {
                foreach( $form->fields as $f ) {
                    if( $f->parameters['hash'] == $hash )$arrangedFields[] = $f;
                }
            }
            $form->fields = $arrangedFields;
        }
        foreach( $form->fields as $f ) {

            $fieldHash = $f->parameters['hash'];
            //Is this field among requested fields?
            if( in_array( $fieldHash, $requestedFieldsArray )) {

                //Add to confirmed fields
                $confirmedFieldsArray[] = $fieldHash;

                //If we have search criteria ,send it to the plugin handler
                if( $criteria != null && property_exists($criteria, $fieldHash) ) {

                    //Grab the particular search criteria for this field
                    $fieldCriteria = $criteria->$fieldHash;
                    if($fieldCriteria == null)continue;


                    //Plugin handler is supposed to clean up the keyword and return a "where" expression
                    $w = $pManager->invokeMethod('getSQL', JFORM_PLUGIN_ELEMENT,
                            array($f->type), array($f , $fieldCriteria ) );
                    if( $w != '' ) {
                        $whereFragments[] = $w;
                    }
                }
            }
        }

        //Process core fields "id, uid"
        if( in_array('id', $requestedFieldsArray )) {
            if( $criteria && property_exists($criteria, 'id')) {
                JArrayHelper::toInteger( $criteria->id->numbers  );
                $ids = implode( ',', $criteria->id->numbers );
                $op = $criteria->id->mode=='or'?' OR ':' AND ';
                $whereFragments[] = ' `id` IN ( '.$ids.') '.$op;
            }
        }

        if( in_array('uid', $requestedFieldsArray ) ) {
            if( $criteria && property_exists($criteria, 'uid')) {
                JArrayHelper::toInteger( $criteria->uid->numbers  );
                $ids = implode( ',', $criteria->uid->numbers );
                $op  = $criteria->uid->mode=='or'?' OR ':' AND ';
                $whereFragments[] = ' `uid` IN ( '.$ids.') '.$op;
            }
        }


        $whereSQL = '';
        if( count($whereFragments) ) {

            //Build Where part of the query
            $whereSQL = ' WHERE '. implode( ' ' , $whereFragments );
            $whereSQL = trim( $whereSQL, ' AND ');
            $whereSQL = trim( $whereSQL, ' OR ');
        }


        //Get total count of records that match the request
        $db =& JFactory::getDBO();
        $sql =  "SELECT COUNT(*) FROM #__jforms_".$tableName." $whereSQL";
        $db->setQuery($sql);
        $count = $db->loadResult();

        //Apply limits
        $limit = '';
        if( $start != -1 && $rpp != -1 ) {
            $limit = " LIMIT $start, $rpp ";
        }

        //Join confirmed fields
        $fields = implode(',', $confirmedFieldsArray );

        //Build query
        $sql =  "SELECT $fields FROM #__jforms_".$tableName." $whereSQL $limit";

        $db =& JFactory::getDBO();
        $db->setQuery($sql);
        return array(
                'total' 		=> $count,
                'loaded_fields' => $confirmedFieldsArray,
                'records' 		=> $db->loadRowList()
        );

    }

    function onFormCreate( &$form, $elementPlugins ) {

        $db =& JFactory::getDBO();

        //Automatically generate Tablename
        //And save table name to plugin parameters for this form
        $form['storagePluginParameters']['Database']['tableName'] = substr( md5(uniqid(rand(), true)), 0, 5 );

        //Short name
        $tableName = $form['storagePluginParameters']['Database']['tableName'];

        $sql  = 'CREATE TABLE `#__jforms_'.$tableName.'` ('.
                ' `id` INT(11) NOT NULL AUTO_INCREMENT ,'
                .' `uid` INT(11) UNSIGNED NOT NULL DEFAULT "0",';

        $columnSQL = array();

        $hashes = array();

        foreach( $form['fieldInformation'] as $f ) {

            //Never mind fields with no storage requirments
            if($elementPlugins[$f->type]->storage == null)continue;

            //Will take the first storage field from XML paramters for now
            $storage = $elementPlugins[$f->type]->storage;

            if( $storage->requirefs ) {
                JFormSPluginDatabase::_createFSField( $tableName, $f->hash );
            }

            $columnSQL[] = JFormSPluginDatabase::_getSQLField($storage, $f->hash);


            $field 		      = new stdClass();
            $field->type 	  = $storage->type;
            $field->size 	  = $storage->size;
            $field->requirefs = $storage->requirefs;

            $hashes[strval($f->hash)] = $field;

        }
        $form['storagePluginParameters']['Database']['fields'] = base64_encode(serialize($hashes )) ;
        $sql .= implode( ',', $columnSQL );
        $sql .= ' ,PRIMARY KEY ( `id` )'.
                ' )  TYPE=InnoDB;';

        $db->setQuery($sql);

        if(	$db->query() ) {
            return true;
        }
        return false;

    }

    function onFormSave( &$form, $elementPlugins ) {



        $databaseParameters = $form['storagePluginParameters']['Database'];
        $tableName = $databaseParameters['tableName'];

        $sqlBlocks  = array();

        //This parameter is used to save the current Table structure, To avoid additional queries to DB
        //It is used to compare the current table structure with incoming form fields to see if they match and add/modify/remove table fields if needed
        $oldFields = unserialize(base64_decode( $databaseParameters['fields']));


        foreach( $form['fieldInformation'] as $f ) {

            //Never mind fields with no DB storage requirments
            if($elementPlugins[$f->type]->storage == null)continue;

            //Is this an existant field?
            if( array_key_exists( $f->hash , $oldFields ) ) {

                $currentStorage  = $elementPlugins[$f->type]->storage;
                $previousStorage = $oldFields[$f->hash];

                //Creates Filesystem requirements for the field
                if( $currentStorage->requirefs != $previousStorage->requirefs ) {
                    if( $currentStorage->requirefs ) {
                        JFormSPluginDatabase::_createFSField( $tableName, $f->hash );
                    } else {
                        JFormSPluginDatabase::_deleteFSField( $tableName, $f->hash );
                    }
                }

                //See if this Field has been altered "e.g. from Textfield to textarea"
                if( $currentStorage->type != $previousStorage->type ||
                        $currentStorage->size != $previousStorage->size  ) {


                    //Alter table to meet new settings
                    $sqlBlocks[] = 'MODIFY'.JFormSPluginDatabase::_getSQLField($currentStorage, $f->hash);

                    //Update table sturcture parameter for this plugin
                    $oldFields[$f->hash]->type	     = $currentStorage->type;
                    $oldFields[$f->hash]->size 		 = $currentStorage->size;
                    $oldFields[$f->hash]->requirefs  = $currentStorage->requirefs;


                } else {
                    //Field already exists and has the same settings, do nothing then
                    continue;
                }
                //New Field
            } else {
                //See what storage requirements does it need
                $currentStorage = $elementPlugins[$f->type]->storage;

                //Create FS (FileSystem) Requirements for this field
                if( $storageInfo != null && $storageInfo->requirefs ) {
                    JFormSPluginDatabase::_createFSField( $tableName, $f->hash );
                }

                //Generate SQL
                $sqlBlocks[] = 'ADD'.JFormSPluginDatabase::_getSQLField($currentStorage, $f->hash);

                //Update table sturcture parameter for this plugin
                $oldFields[$f->hash]            = new stdClass();
                $oldFields[$f->hash]->type      = $currentStorage->type;
                $oldFields[$f->hash]->size      = $currentStorage->size;
                $oldFields[$f->hash]->requirefs = $currentStorage->requirefs;
            }
        }
        //Check for deleted fields
        foreach($oldFields as $oldFieldHash => $storageInfo) {
            foreach( $form['fieldInformation'] as $newField ) {
                //Never mind fields with no DB storage requirments
                $deleted = true;
                if($elementPlugins[$newField->type]->storage == null)continue;


                if( $oldFieldHash == $newField->hash ) {
                    //Field exists
                    $deleted = false;
                    break;
                }
            }
            //Field is present in the previous table sturcture but no longer exist in the incoming data (i.e. Deleted)
            if( $deleted  ) {

                //Remove FS allocated for this field
                if( $storageInfo != null && $storageInfo->requirefs ) {
                    JFormSPluginDatabase::_deleteFSField( $tableName, $oldFieldHash );
                }


                //Construct query
                $sqlBlocks[] = 'DROP COLUMN `'. $oldFieldHash . '`';

                //Update table sturcture parameter for this plugin
                unset($oldFields[$oldFieldHash]);
            }
        }


        //No changes in Table structure
        if(!count($sqlBlocks)) {
            //return harmlessly
            return true;
        }
        //Build SQL query and execute it
        $sql  = 'ALTER TABLE `#__jforms_'.$tableName.'` ';
        $sql .= implode( ',' , $sqlBlocks );

        $db =& JFactory::getDBO();

        $db->setQuery($sql);


        if(	$db->query() ) {
            //Modify the table structure parameter for the plugin
            $form['storagePluginParameters']['Database']['fields'] = base64_encode(serialize( $oldFields )) ;
            return true;
        }
        return false;


    }

    function onFormDelete( $form, $additionalParameters ) {
        //Delete records and drop table
        JFormSPluginDatabase::deleteRecords( $form, null, true );
    }

    function deleteRecords( $form , $ids, $drop=false ) {

        $db =& JFactory::getDBO();
        $tableName = $form->storagePluginParameters['Database']['tableName'];

        if( is_array( $ids ) && count( $ids ) && !$drop ) {

            $idList = implode( ',' ,$ids );

            //Delete Selected records only from FS and DB
            JFormSPluginDatabase::_deleteFSRecords( $tableName, $ids );
            $db->setQuery('DELETE FROM `#__jforms_'.$tableName.'` WHERE id IN ('.$idList.')');

        } else {

            //Should we drop the table as well?
            if( $drop ) {
                //Drop FS and DB Tables
                JFormSPluginDatabase::_deleteFSTable( $tableName );
                $db->setQuery('DROP TABLE `#__jforms_'.$tableName.'`');
            } else {
                //delete all records
                JFormSPluginDatabase::_deleteFSRecords( $tableName, null );
                $db->setQuery('DELETE FROM `#__jforms_'.$tableName.'`');
            }
        }

        if(	$db->query() ) {
            return true;
        }
        return false;

    }


    /**
     * Retrieves records from DB based on ids
     *
     * @access	public
     * @param object $form : The form to retrieve records from
     * @param array $ids : Record Ids , if null the method will return all records
     * @return array , Returns records on success, null on failure
     */
    function getRecords( $form, $ids ) {


        $db   = & JFactory::getDBO();
        $tableName = $form->storagePluginParameters['Database']['tableName'];


        if( is_array( $ids ) && count( $ids ) ) {
            $idList = implode( ',' , $ids );
            $db->setQuery('SELECT * FROM `#__jforms_'.$tableName.'` WHERE id IN ('.$idList.')');
        } else {
            $db->setQuery('SELECT * FROM `#__jforms_'.$tableName.'`');
        }

        return $db->loadObjectList();
    }

    /**
     * Saves a record to DB
     *
     * @access	public
     * @param object $form : The form we're saving this record under
     * @param array $data : Data to be saved, indexed by hash values
     * @return	bool , true on success,  false on failure
     */
    function saveRecord( $form, $data,VA_Form_Form $jForm = null ) {

        global $JFormGlobals;
        $pluginManager =& $JFormGlobals['JFormsPlugin'];

        if( $form == null || $data == null ) {
            return false;
        }

        //Load Storage Plugins
        $pluginManager->loadStoragePlugins();
        $pluginManager->loadElementPlugins();


        $db     =& JFactory::getDBO();
        $config	=& JFactory::getConfig();
        $now	=& JFactory::getDate();

        $tableName  = $form->storagePluginParameters['Database']['tableName'];
        if(!is_null($jForm)) $elements = $jForm->getAssocValues();

        //Loop through all fields of the current form
        foreach( $form->fields as $f ) {

            //If a certain field has no storage requirements
            if( $pluginManager->element_plugins[$f->type]->storage == null ) {
                //Ignore it
                continue;
            }
            //Otherwise retreive its value from the request ,clean it up and save it
            $fname = $f->parameters['hash'];

            $fieldArray[] = $fname;

            //Clean-up content
            $value = $data[$fname];
            if(key_exists($fname, $elements)) {
                $v = $elements[$fname];
                $value = is_array($v)?join(',',$v):$v;
            }
            $valueArray[] = $db->quote( $db->getEscaped( $value, false ), false );
        }

        //Inserts user ID and processes Profile mode forms
        $uid = intval( $data['uid'] );
        if( $form->type == JFORMS_TYPE_PROFILE ) {
            if( $uid != 0 ) {
                $sql = 'SELECT id FROM `#__jforms_'.$tableName.'` WHERE uid='.$uid;
                $db->setQuery($sql);
                $fieldId = $db->loadResult();
                JFormSPluginDatabase::_deleteFSRecords( $tableName, array( $fieldId ) );
                $sql = 'DELETE FROM `#__jforms_'.$tableName.'` WHERE uid='.$uid;
                $db->setQuery($sql);
                $db->query();
            }
        }
        $fieldArray[] = 'uid';
        $valueArray[] = $uid;

        //Build Query
        $fields = implode( ',', $fieldArray );
        $values = implode( ',', $valueArray );
        $sql = 'INSERT INTO `#__jforms_'.$tableName.'` ( '.$fields.' ) VALUES ( '.$values.' )';
        $db->setQuery($sql);

        //Execute it
        if(	$db->query() ) {
            return true;
        }
        return false;

    }
}
